package com.navi.loaddata.loadService.impl.hisToGp;

import com.navi.loaddata.core.errorLog.ErrorCode;
import com.navi.loaddata.core.etlException.ETLException2;
import com.navi.loaddata.core.gpEntity.MOpeD;
import com.navi.loaddata.core.gpEntity.MProdD;
import com.navi.loaddata.core.gpEntity.hisToGP.ProductHis;
import com.navi.loaddata.core.gpEntity.hisToGP.WppShtOpeHisExt;
import com.navi.loaddata.core.mysqlEntity.EtlConfDModel;
import com.navi.loaddata.core.repository.impl.GreenPlumRepositoryImpl;
import com.navi.loaddata.core.repository.impl.WmsRepositoryImpl;
import com.navi.loaddata.core.util.JdbcUtil;
import com.navi.loaddata.core.util.LoggerUtil;
import com.navi.loaddata.core.util.ProcessUtil;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.lang.StringUtils;
import org.hibernate.Session;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

/**
 * Created by ghost on 2018/6/12.
 */
@Service
@Transactional(transactionManager = "secondaryPlatformTransactionManager")
public class LoadWmsInfoService {
    private static final Logger logger = LoggerFactory.getLogger(LoadWmsInfoService.class);
    private LoggerUtil loggerUtil = new LoggerUtil(this.getClass());
    @Autowired
    private GreenPlumRepositoryImpl gpDao;

    public List<ProductHis> getWmsShipInfo(EtlConfDModel etlConfD) throws Exception {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
        String startTime = sdf.format(etlConfD.getRunStartTimestamp().getTime());
        String endTime = sdf.format(etlConfD.getRunEndTimestamp().getTime());

        List<ProductHis> productHisList = new ArrayList<>();

        QueryRunner qr_wms = JdbcUtil.getQueryRunner_wms();
        try {
            String sql = "SELECT" +
                    " glass_id_o," +
                    " prod_id," +
                    " sn," +
                    " grade_wns_ship," +
                    " pallet_id," +
                    " ppbox_id," +
                    " In_WMS_PO_ID," +
                    " SalesOrder_ID," +
                    " logof_timestamp" +
                    " FROM (SELECT" +
                    " prd_id as glass_id_o," +
                    " PRD_SPEC_ID AS prod_id," +
                    " sn_id AS sn," +
                    " PRD_GRADE AS grade_wns_ship," +
                    " lot_id as pallet_id," +
                    " crr_id as ppbox_id," +
                    " PO_ID as In_WMS_PO_ID," +//+栏位
                    " SO_ID as SalesOrder_ID," +//+栏位
                    " SHIPPING_DATE as logof_timestamp," +
                    " LAG(1,1,0) OVER(partition BY prd_id,prd_state ORDER BY timekey DESC) AS FLG" +
                    " FROM" +
                    " prod_prod_HIST" +
                    " WHERE" +
                    " prd_state in('Shipped','Received')" +
                    " and TIMEKEY>='" + startTime + "'" +
                    " and TIMEKEY<'" + endTime + "'" +
                    " )WHERE FLG=0";


            productHisList = qr_wms.query(sql, new BeanListHandler<ProductHis>(ProductHis.class));
        } catch (Exception e) {
            throw new ETLException2(ErrorCode.ERROR_GET_WMS_HIS_SHIP, e);
        }
        return productHisList;
    }


    public WppShtOpeHisExt getExtInfo(String panelId) {
        WppShtOpeHisExt wppExt = new WppShtOpeHisExt();

        try {
            List<WppShtOpeHisExt> wppExtList = gpDao.list(WppShtOpeHisExt.class, "from WppShtOpeHisExt where panelId='" + panelId + "'");
            if (wppExtList.size() > 0) {//存在
                wppExt = wppExtList.get(0);
            } else {
                wppExtList = gpDao.list(WppShtOpeHisExt.class, "from WppShtOpeHisExt where panelId='" + panelId.substring(0, 10) + "'");
                if (wppExtList.size() > 0) {
                    wppExt.setPanelId(panelId);
                    wppExt.setSn(ProcessUtil.getStringS(wppExtList.get(0).getSn()));
                    wppExt.setBareGlassId(ProcessUtil.getStringS(wppExtList.get(0).getBareGlassId()));
                    wppExt.setBareProduct(ProcessUtil.getStringS(wppExtList.get(0).getBareProduct()));
                    wppExt.setProductIdErp(ProcessUtil.getStringS(wppExtList.get(0).getProductIdErp()));
                    wppExt.setWorkorderId(ProcessUtil.getStringS(wppExtList.get(0).getWorkorderId()));
                    wppExt.setWorkorderIdRtp(ProcessUtil.getStringS(wppExtList.get(0).getWorkorderIdRtp()));
                    wppExt.setThickness(ProcessUtil.getBigDecimal(wppExtList.get(0).getThickness()));
                    wppExt.setGlassJudgeAry(ProcessUtil.getStringS(wppExtList.get(0).getGlassJudgeAry()));
                    wppExt.setGlassGradeAry(ProcessUtil.getStringS(wppExtList.get(0).getGlassGradeAry()));
                    wppExt.setTftPnlGrade(ProcessUtil.getStringS(wppExtList.get(0).getTftPnlGrade()));
                    wppExt.setGlassJudgeCf(ProcessUtil.getStringS(wppExtList.get(0).getGlassJudgeCf()));
                    wppExt.setGlassGradeCf(ProcessUtil.getStringS(wppExtList.get(0).getGlassGradeCf()));
                    wppExt.setCfPnlGrade(ProcessUtil.getStringS(wppExtList.get(0).getCfPnlGrade()));
                    wppExt.setOcPnlGradeCt1(ProcessUtil.getStringS(wppExtList.get(0).getOcPnlGradeCt1()));
                    wppExt.setOcPnlGradeCInsp(ProcessUtil.getStringS(wppExtList.get(0).getOcPnlGradeCInsp()));
                    wppExt.setGradeWnsShip(ProcessUtil.getStringS(wppExtList.get(0).getGradeWnsShip()));
                    wppExt.setOcPnlLosscodeCt1(ProcessUtil.getStringS(wppExtList.get(0).getOcPnlLosscodeCt1()));
                    wppExt.setOcPnlLosscodeCInsp(ProcessUtil.getStringS(wppExtList.get(0).getOcPnlLosscodeCInsp()));
                    wppExt.setPolLotIdTft(ProcessUtil.getStringS(wppExtList.get(0).getPolLotIdTft()));
                    wppExt.setPolPartNoTft(ProcessUtil.getStringS(wppExtList.get(0).getPolPartNoTft()));
                    wppExt.setPolLotIdCf(ProcessUtil.getStringS(wppExtList.get(0).getPolLotIdCf()));
                    wppExt.setPolPartNoCf(ProcessUtil.getStringS(wppExtList.get(0).getPolPartNoCf()));
                    wppExt.setPpboxId(ProcessUtil.getStringS(wppExtList.get(0).getPpboxId()));
                    wppExt.setPalletId(ProcessUtil.getStringS(wppExtList.get(0).getPalletId()));
                    wppExt.setDestCustomerId(ProcessUtil.getStringS(wppExtList.get(0).getDestCustomerId()));

                } else {
                    wppExt.setPanelId(panelId);
                }

            }
        } catch (Exception e) {
            throw new ETLException2(ErrorCode.ERROR_GET_GP_OPE_HIS_EXT,e);
        }
        return wppExt;
    }


    private String shopForSql(String shop) {
        if (StringUtils.isEmpty(shop)) return "";
        return " AND TrackOut.SUBFACTORYNAME like '" + StringUtils.replace(shop, "*", "%") + "'";
    }

    private String opeForSql(String ope) {
        if (StringUtils.isEmpty(ope)) return "";
        return " AND TrackOut.OLDPROCESSOPERATIONNAME like'" + StringUtils.replace(ope, "*", "%") + "'";
    }

    private String eqptForSql(String eqpt) {
        if (StringUtils.isEmpty(eqpt)) return "";
        return " AND TrackOut.MACHINENAME like'" + StringUtils.replace(eqpt, "*", "%") + "'";
    }


    public void clearObject(WppShtOpeHisExt wppExt) {
        Session session = gpDao.getEntityManagerSession();
        session.evict(wppExt);//防止HIBERNATE自动保存实体
    }






    private String caseWhen4100(String ope_id, String glass_id_o) {
        if ("L4100".equals(ope_id)) return glass_id_o.substring(0, 10);
        return glass_id_o;
    }
}
